﻿using Kingdee.BOS.App.Data;
using Kingdee.BOS;
using Kingdee.BOS.Contracts.Report;
using Kingdee.BOS.Core.Report;
using Kingdee.BOS.Util;
using Kingdee.BOS.Core;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Kingdee.BOS.Orm.DataEntity;
using Kingdee.BOS.Core.DynamicForm.PlugIn.Args;

namespace NS
{
    [HotUpdate]
    [Description("海狸对货代每月应付款")]
    public class HLDReport : SysReportBaseService
    {
        /// 初始化事件：在此事件中，设置报表基本属性
        public override void Initialize()
        {
            base.Initialize();
            // 简单账表类型：普通、树形、分页
            this.ReportProperty.ReportType = ReportType.REPORTTYPE_NORMAL;
            this.ReportProperty.BillKeyFieldName = "XH";
            // 报表名称
            this.ReportProperty.ReportName = new LocaleValue("海狸对货代每月应付款", base.Context.UserLocale.LCID);
            this.IsCreateTempTableByPlugin = true;
            this.ReportProperty.IsUIDesignerColumns = false;
            this.ReportProperty.IsGroupSummary = true;
            this.ReportProperty.SimpleAllCols = false;
            // 单据主键：两行FID相同，则为同一单的两条分录，单据编号可以不重复显示
            //this.ReportProperty.PrimaryKeyFieldName = "XH";
            this.ReportProperty.IsDefaultOnlyDspSumAndDetailData = false;
            // 报表主键字段名：默认为FIDENTITYID，可以修改
            this.ReportProperty.IdentityFieldName = "FIDENTITYID";
            // 设置精度控制
            //List list = new List();
            //// 数量
            //list.Add(new DecimalControlField
            //{
            //    ByDecimalControlFieldName = "FQty",
            //    DecimalControlFieldName = "FUnitPrecision"
            //});
            //// 单价
            //list.Add(new DecimalControlField
            //{
            //    ByDecimalControlFieldName = "FTAXPRICE",
            //    DecimalControlFieldName = "FPRICEDIGITS"
            //});
            //// 金额
            //list.Add(new DecimalControlField
            //{
            //    ByDecimalControlFieldName = "FALLAMOUNT",
            //    DecimalControlFieldName = "FAMOUNTDIGITS"
            //});
            //this.ReportProperty.DecimalControlFieldList = list;
        }

        public override string GetTableName()
        {
            var result = base.GetTableName();
            return result;
        }

        public override void BuilderReportSqlAndTempTable(IRptParams filter, string tableName)
        {
            base.BuilderReportSqlAndTempTable(filter, tableName);
            // 拼接过滤条件 ： filter
            // 略
            // 默认排序字段：需要从filter中取用户设置的排序字段
            string seqFld = string.Format(base.KSQL_SEQ, "货代");

            DateTime FStartDate;
            DateTime FEndDate;
            string F_WNEG_CheckBox = "";

            DynamicObject dyFilter = filter.FilterParameter.CustomFilter;
            StringBuilder SQLFilter = new StringBuilder();
            StringBuilder SQLFilter1 = new StringBuilder();
            FStartDate = Convert.ToDateTime(dyFilter["FStartDate"]);//开始日期
            //FEndDate = Convert.ToDateTime(dyFilter["FEndDate"]);//结束
            if (!FStartDate.ToString().Contains("0001"))
            {
                SQLFilter.Append(" and t.FDATE<=DATEADD(day, -1, DATEADD(month, DATEDIFF(month, -1,'" + FStartDate + "'), 0)) and t.FDATE>=DATEADD(month, DATEDIFF(month, 0, '" + FStartDate + "'), 0)");
            }
            if (!FStartDate.ToString().Contains("0001"))
            {
                SQLFilter1.Append(" and t.FDATE<=DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, '" + FStartDate + "'), 0))  and t.FDATE>=DATEADD(MONTH, DATEDIFF(MONTH, 0, '" + FStartDate + "') - 1, 0)");
            }
            ////过滤 日期
            //if (!FStartDate.ToString().Contains("0001") && !FEndDate.ToString().Contains("0001"))
            //{
            //    SQLFilter.Append("and t.FDATE>='" + FStartDate + "' and t.FDATE<='" + FEndDate + "'");
            //}
            ////if (!dyFilter.IsNullOrEmpty())
            ////{
            //    if (!Convert.ToString(dyFilter["FMaterialid_id"]).Equals("0"))
            //    {
            //        SQLFilter1.Append("and t1.FMATERIALID='" + dyFilter["FMaterialid_id"] + "'");
            //    }
            //    if (!Convert.ToString(dyFilter["FSuppiler_id"]).Equals("0"))
            //    {
            //        SQLFilter1.Append("and t6.FSUPPLIERID='" + dyFilter["FSuppiler_id"] + "'");
            //    }
            //    if (!Convert.ToString(dyFilter["FOrgId_id"]).Equals("0"))
            //    {
            //        SQLFilter1.Append("and t.FPURCHASEORGID='" + dyFilter["FOrgId_id"] + "'");
            //    }
            //if (!Convert.ToString(dyFilter["FBillNo"]).Equals(""))
            //{
            //    SQLFilter1.Append("and t.FBillNo='" + dyFilter["FBillNo"] + "'");
            //}
            //}

            // 取数SQL
            string sql = string.Format(@"/*dialect*/
select t.货代,sum(t.合计应付RMB)合计应付RMB,sum(t.上一月已付)上一月已付,t1.日期,t1.金额,(sum(t.合计应付RMB)-sum(t.上一月已付)-sum(t.本月已付))剩余应付金额 INTO #A
from (
select t3.FNAME 货代,sum(t2.FAPPLYAMOUNTFOR*t.FEXCHANGERATE)合计应付RMB,0 上一月已付,0 本月已付,''日期,0 金额 from T_CN_PAYAPPLY t
left join T_ORG_ORGANIZATIONS_L t1 on t.FAPPLYORGID=t1.forgid
left join T_CN_PAYAPPLYENTRY t2 on t.fid=t2.fid
LEFT JOIN T_BD_SUPPLIER_L T3 ON T.FCONTACTUNIT=T3.FSUPPLIERID
where t1.FLOCALEID=2052 and t1.fname='海狸' and t.FDOCUMENTSTATUS='C' {1}
AND t3.FNAME IS NOT NULL
group by t3.fname
union all
SELECT t3.FNAME 货代,0 合计应付RMB,sum(t1.FREALPAYAMOUNT)上一月已付,0 本月已付,''日期,0 金额 FROM T_AP_PAYBILL T
LEFT JOIN T_AP_PAYBILLENTRY T1 ON T.FID=T1.FID
LEFT JOIN T_ORG_ORGANIZATIONS_L T2 ON T.FPAYORGID=T2.forgid
LEFT JOIN T_BD_SUPPLIER_L T3 ON T.FCONTACTUNIT=T3.FSUPPLIERID
where t2.FLOCALEID=2052 and t2.fname='海狸' and t.FDOCUMENTSTATUS='C' {2}
AND t3.FNAME IS NOT NULL
group by t3.fname
union all
SELECT t3.FNAME 货代,0 合计应付RMB,0 上一月已付,sum(t1.FREALPAYAMOUNT)本月已付,''日期,0 金额 FROM T_AP_PAYBILL T
LEFT JOIN T_AP_PAYBILLENTRY T1 ON T.FID=T1.FID
LEFT JOIN T_ORG_ORGANIZATIONS_L T2 ON T.FPAYORGID=T2.forgid
LEFT JOIN T_BD_SUPPLIER_L T3 ON T.FCONTACTUNIT=T3.FSUPPLIERID
where t2.FLOCALEID=2052 and t2.fname='海狸' and t.FDOCUMENTSTATUS='C' {1}
AND t3.FNAME IS NOT NULL
group by t3.fname
)t 
left join (
SELECT t3.FNAME 货代,0 合计应付RMB,0 上一月已付,t.fdate 日期,t1.FREALPAYAMOUNT 金额 FROM T_AP_PAYBILL T
LEFT JOIN T_AP_PAYBILLENTRY T1 ON T.FID=T1.FID
LEFT JOIN T_ORG_ORGANIZATIONS_L T2 ON T.FPAYORGID=T2.forgid
LEFT JOIN T_BD_SUPPLIER_L T3 ON T.FCONTACTUNIT=T3.FSUPPLIERID
where t2.FLOCALEID=2052 and t2.fname='海狸' and t.FDOCUMENTSTATUS='C' {1}
AND t3.FNAME IS NOT NULL
)t1 on t.货代=t1.货代
group by t.货代,t1.日期,t1.金额


SELECT 货代,合计应付RMB,上一月已付,日期,SUM(金额)金额,剩余应付金额
,ROW_NUMBER() over( partition by 货代 order by  日期)XH INTO #B
FROM #A
GROUP BY 货代,合计应付RMB,上一月已付,日期,剩余应付金额

UPDATE #B SET 合计应付RMB=0,上一月已付=0,剩余应付金额=0 WHERE XH<>1
SELECT * ,{3} INTO {0} FROM #B



                                        ", tableName, SQLFilter, SQLFilter1, seqFld);
                                        DBUtils.ExecuteDynamicObject(this.Context, sql);


    //        sql = string.Format(@"/*dialect*/UPDATE {0} SET 合计应付RMB=0,上一月已付=0,剩余应付金额=0 WHERE XH<>1
			 //", tableName);
    //        DBUtils.Execute(this.Context, sql);
        }

        protected override string GetIdentityFieldIndexSQL(string tableName) 
        {
            string result = base.GetIdentityFieldIndexSQL(tableName);
            return result;
        }

        //protected override void ExecuteBatch(List listSql) { base.ExecuteBatch(listSql); }
        public override ReportHeader GetReportHeaders(IRptParams filter)
        {
            ReportHeader header = new ReportHeader();
            var DJBH = header.AddChild("货代", new LocaleValue("货代"));
            DJBH.ColIndex = 0;
            DJBH.Width = 180;
            var CGZZ = header.AddChild("合计应付RMB", new LocaleValue("合计应付RMB"), SqlStorageType.SqlDecimal);
            CGZZ.ColIndex = 1;
            var CGRQ = header.AddChild("上一月已付", new LocaleValue("上一月已付"), SqlStorageType.SqlDecimal);
            CGRQ.ColIndex = 2;
            var CGY = header.AddChild("日期", new LocaleValue("日期"));
            CGY.ColIndex = 3;
            var WLGYS = header.AddChild("金额", new LocaleValue("金额"), SqlStorageType.SqlDecimal);
            WLGYS.ColIndex = 4;
            var YFHJ = header.AddChild("剩余应付金额", new LocaleValue("剩余应付金额"), SqlStorageType.SqlDecimal);
            YFHJ.ColIndex = 5;
            return header;
        }

        protected override void ExecuteBatch(List<string> listSql)
        {
            base.ExecuteBatch(listSql);
        }

        protected override string GetSummaryColumsSQL(List<SummaryField> summaryFields)
        {
            var result = base.GetSummaryColumsSQL(summaryFields);
            return result;
        }

        protected override System.Data.DataTable GetListData(string sSQL)
        {
            var result = base.GetListData(sSQL);
            return result;
        }

        protected override System.Data.DataTable GetReportData(IRptParams filter)
        {
            var result = base.GetReportData(filter);
            return result;
        }

        protected override System.Data.DataTable GetReportData(string tablename, IRptParams filter)
        {
            var result = base.GetReportData(tablename, filter);
            return result;
        }

        public override int GetRowsCount(IRptParams filter)
        {
            var result = base.GetRowsCount(filter);
            return result;
        }

        protected override string BuilderFromWhereSQL(IRptParams filter)
        {
            string result = base.BuilderFromWhereSQL(filter);
            return result;
        }

        protected override string BuilderSelectFieldSQL(IRptParams filter)
        {
            string result = base.BuilderSelectFieldSQL(filter);
            return result;
        }

        protected override string BuilderTempTableOrderBySQL(IRptParams filter)
        {
            string result = base.BuilderTempTableOrderBySQL(filter);
            return result;
        }

        public override void CloseReport()
        {
            base.CloseReport();
        }

        protected override string CreateGroupSummaryData(IRptParams filter, string tablename)
        {
            string result = base.CreateGroupSummaryData(filter, tablename);
            return result;
        }

        protected override void CreateTempTable(string sSQL)
        {
            base.CreateTempTable(sSQL);
        }

        public override void DropTempTable()
        {
            base.DropTempTable();
        }

        public override System.Data.DataTable GetList(IRptParams filter)
        {
            var result = base.GetList(filter);
            return result;
        }

        public override List<long> GetOrgIdList(IRptParams filter)
        {
            var result = base.GetOrgIdList(filter);
            return result;
        }

        public override List<Kingdee.BOS.Core.Metadata.TreeNode> GetTreeNodes(IRptParams filter)
        {
            var result = base.GetTreeNodes(filter);
            return result;
        }
    }
}
